﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entities;

namespace DataAccess
{
    public class InsurancePolicyAccess
    {
        public string AddPolicyInsurance(InsurancePolicyInfo policy)
        {
            InsuranceSystemDataContext db = new InsuranceSystemDataContext();
            var insu = db.InsurancePolicies.Where(i => i.PerVehicleID.Equals(policy.PerVehicleID) &&
                i.InsuranceID.Equals(policy.InsuranceID)).SingleOrDefault();

            if (insu == null)
            {
                InsurancePolicy ip = new InsurancePolicy();

                ip.CustomerID = policy.CustomerID;
                ip.PerVehicleID = policy.PerVehicleID;
                ip.InsuranceID = policy.InsuranceID;
                ip.PolicyDate = policy.PolicyDate;
                ip.PolicyDuration = policy.PolicyDuration;
                ip.InsuredAmount = policy.InsuredAmount;
                ip.Status = policy.Status;

                db.InsurancePolicies.InsertOnSubmit(ip);
                db.SubmitChanges();
                return "You buy insurance successful";
            }
            else
            {
                return "Failed. \nYou bought this Insurance type for your vehicle before.";
            }

        }

        public List<InsurancePolicyInfo> getListOfPolicyByCustID(int custID)
        {
            InsuranceSystemDataContext db = new InsuranceSystemDataContext();
            var result = from i in db.InsurancePolicies
                         where i.CustomerID == custID
                         select new
                         InsurancePolicyInfo
                         {
                             PolicyID = i.PolicyID,
                             CustomerID = Convert.ToInt32(i.CustomerID),
                             PerVehicleID = Convert.ToInt32(i.PerVehicleID),
                             InsuranceID = Convert.ToInt32(i.InsuranceID),
                             PolicyDate = Convert.ToDateTime(i.PolicyDate),
                             PolicyDuration = i.PolicyDuration,
                             InsuredAmount = Convert.ToSingle(i.InsuredAmount),
                             Status = i.Status
                         };
            return result.ToList();
        }

        public List<InsurancePolicyInfo> getAllPolicy()
        {
            InsuranceSystemDataContext db = new InsuranceSystemDataContext();
            var result = from policy in db.InsurancePolicies
                         join c in db.Customers on policy.CustomerID equals c.CustomerID
                         join i in db.InsuranceTypes on policy.InsuranceID equals i.InsuranceID
                         select new
                         InsurancePolicyInfo
                         {
                             PolicyID = policy.PolicyID,
                             CustomerID = Convert.ToInt32(policy.CustomerID),
                             CustomerName = c.Firstname + " " + c.Lastname,

                             PerVehicleID = Convert.ToInt32(policy.PerVehicleID),
                             InsuranceID = Convert.ToInt32(policy.InsuranceID),
                             InsuranceType = i.InsuranceName,
                             PolicyDate = Convert.ToDateTime(policy.PolicyDate),
                             PolicyDuration = policy.PolicyDuration,
                             InsuredAmount = Convert.ToSingle(policy.InsuredAmount),
                             Status = policy.Status
                         };
            return result.ToList();
        }

        public List<InsurancePolicyInfo> getListOfPolicyByDate(DateTime startDate, DateTime endDate)
        {
            InsuranceSystemDataContext db = new InsuranceSystemDataContext();
            var result = from policy in db.InsurancePolicies
                         join c in db.Customers on policy.CustomerID equals c.CustomerID
                         join i in db.InsuranceTypes on policy.InsuranceID equals i.InsuranceID
                         where policy.PolicyDate >= startDate && policy.PolicyDate <= endDate
                         select new
                         InsurancePolicyInfo
                         {
                             PolicyID = policy.PolicyID,
                             CustomerID = Convert.ToInt32(policy.CustomerID),
                             CustomerName = c.Firstname + " " + c.Lastname,

                             PerVehicleID = Convert.ToInt32(policy.PerVehicleID),
                             InsuranceID = Convert.ToInt32(policy.InsuranceID),
                             InsuranceType = i.InsuranceName,
                             PolicyDate = Convert.ToDateTime(policy.PolicyDate),
                             PolicyDuration = policy.PolicyDuration,
                             InsuredAmount = Convert.ToSingle(policy.InsuredAmount),
                             Status = policy.Status
                         };
            return result.ToList();
        }

        public List<InsurancePolicyInfo> getListOfPolicyExpired()
        {
            InsuranceSystemDataContext db = new InsuranceSystemDataContext();
            var result = from policy in db.InsurancePolicies
                         join c in db.Customers on policy.CustomerID equals c.CustomerID
                         join i in db.InsuranceTypes on policy.InsuranceID equals i.InsuranceID
                         where Convert.ToDateTime(Convert.ToDateTime(policy.PolicyDate).Year + policy.PolicyDuration) < DateTime.Today
                         select new
                         InsurancePolicyInfo
                         {
                             PolicyID = policy.PolicyID,
                             CustomerID = Convert.ToInt32(policy.CustomerID),
                             CustomerName = c.Firstname + " " + c.Lastname,
                             PerVehicleID = Convert.ToInt32(policy.PerVehicleID),
                             InsuranceID = Convert.ToInt32(policy.InsuranceID),
                             InsuranceType = i.InsuranceName,
                             PolicyDate = Convert.ToDateTime(policy.PolicyDate),
                             PolicyDuration = policy.PolicyDuration,
                             InsuredAmount = Convert.ToSingle(policy.InsuredAmount),
                             Status = policy.Status
                         };
            return result.ToList();
        }

        public List<InsurancePolicyInfo> getDetailPolicyByID(int policyID)
        {
            InsuranceSystemDataContext db = new InsuranceSystemDataContext();
          //  InsurancePolicyInfo ipi = new InsurancePolicyInfo();

            var result = from policy in db.InsurancePolicies
                         join c in db.Customers on policy.CustomerID equals c.CustomerID
                         join i in db.InsuranceTypes on policy.InsuranceID equals i.InsuranceID
                         join p in db.PersonalVehicles on policy.PerVehicleID equals p.PerVehicleID
                         where policy.PolicyID == policyID
                         select new 
                         InsurancePolicyInfo
                         {
                             PolicyID = policy.PolicyID,
                             CustomerID = Convert.ToInt32(policy.CustomerID),
                             CustomerName = c.Firstname + " " + c.Lastname,
                             PerVehicleID = Convert.ToInt32(policy.PerVehicleID),
                             
                             PerVehicleControlPlate = p.ControlPlate,
                             InsuranceID = Convert.ToInt32(policy.InsuranceID),
                             InsuranceType = i.InsuranceName,
                             PolicyDate = Convert.ToDateTime(policy.PolicyDate),
                             PolicyDuration = policy.PolicyDuration,
                             InsuredAmount = Convert.ToSingle(policy.InsuredAmount),
                             Status = policy.Status
                         };
            return result.ToList();
        }

        public InsurancePolicyInfo getPolicyByCustID(int custID)
        {
            InsuranceSystemDataContext db = new InsuranceSystemDataContext();
            var pol = db.InsurancePolicies.Where(p => p.CustomerID == custID).SingleOrDefault();
            if (pol != null)
            {
                InsurancePolicyInfo p = new InsurancePolicyInfo();

                p.PolicyID = pol.PolicyID;
                p.CustomerID = Convert.ToInt32(pol.CustomerID);
                p.PerVehicleID = Convert.ToInt32(pol.PerVehicleID);
                p.InsuranceID = Convert.ToInt32(pol.InsuranceID);
                p.PolicyDate = Convert.ToDateTime(pol.PolicyDate);
                p.PolicyDuration = pol.PolicyDuration;
                p.InsuredAmount = Convert.ToSingle(pol.InsuredAmount);
                p.Status = pol.Status;

                return p;
            }
            else
            {
                return null;
            }
        }

        public void RenewalInsurance(InsurancePolicyInfo ipi)
        {
            InsuranceSystemDataContext db = new InsuranceSystemDataContext();
            var ip = db.InsurancePolicies.Where(i => i.PolicyID == ipi.PolicyID).SingleOrDefault();

            if (ip != null) // found
            {
                ip.PolicyDuration = ipi.PolicyDuration;
                ip.InsuredAmount = ipi.InsuredAmount;
                ip.Status = ipi.Status;

                db.SubmitChanges();
            }
        }

        public InsurancePolicyInfo getPolicyByVehicleAndInsuranceType(int personVehicle, int insuranceID)
        {
            InsuranceSystemDataContext db = new InsuranceSystemDataContext();
            var pol = db.InsurancePolicies.Where(i => i.PerVehicleID.Equals(personVehicle) &&
                i.InsuranceID.Equals(insuranceID)).SingleOrDefault();
            if (pol != null)
            {
                InsurancePolicyInfo p = new InsurancePolicyInfo();

                p.PolicyID = pol.PolicyID;
                p.CustomerID = Convert.ToInt32(pol.CustomerID);
                p.PerVehicleID = Convert.ToInt32(pol.PerVehicleID);
                p.InsuranceID = Convert.ToInt32(pol.InsuranceID);
                p.PolicyDate = Convert.ToDateTime(pol.PolicyDate);
                p.PolicyDuration = pol.PolicyDuration;
                p.InsuredAmount = Convert.ToSingle(pol.InsuredAmount);
                p.Status = pol.Status;

                return p;
            }
            else
            {
                return null;
            }
        }

        public InsurancePolicyInfo getPolicyByID(int polID)
        {
            InsuranceSystemDataContext db = new InsuranceSystemDataContext();
            var pol = db.InsurancePolicies.Where(p => p.PolicyID == polID).SingleOrDefault();
            if (pol != null)
            {
                InsurancePolicyInfo p = new InsurancePolicyInfo();

                p.PolicyID = pol.PolicyID;
                p.CustomerID = Convert.ToInt32(pol.CustomerID);
                p.PerVehicleID = Convert.ToInt32(pol.PerVehicleID);
                p.InsuranceID = Convert.ToInt32(pol.InsuranceID);
                p.PolicyDate = Convert.ToDateTime(pol.PolicyDate);
                p.PolicyDuration = pol.PolicyDuration;
                p.InsuredAmount = Convert.ToSingle(pol.InsuredAmount);
                p.Status = pol.Status;

                return p;
            }
            else
            {
                return null;
            }
        }

    }
}
